package com.quyunshuo.module.home.room.dao

import androidx.room.*
import com.quyunshuo.module.home.room.entity.PriorityLevel
import com.quyunshuo.module.home.room.entity.TodoCategory
import com.quyunshuo.module.home.room.entity.TodoEntity
import kotlinx.coroutines.flow.Flow
import java.util.*

@Dao
interface TodoDao {
    // 创建
    @Insert
    suspend fun insert(todo: TodoEntity): Long

    // 批量插入
    @Insert
    suspend fun insertAll(todos: List<TodoEntity>)

    // 更新
    @Update
    suspend fun update(todo: TodoEntity)

    // 删除
    @Delete
    suspend fun delete(todo: TodoEntity)

    // 根据ID删除
    @Query("DELETE FROM todos WHERE id = :id")
    suspend fun deleteById(id: Long)

    // 查询所有待办
    @Query("SELECT * FROM todos ORDER BY dueDate ASC")
    fun getAllTodos(): Flow<List<TodoEntity>>

    // 根据ID查询
    @Query("SELECT * FROM todos WHERE id = :id")
    suspend fun getTodoById(id: Long): TodoEntity?

    // 根据分类查询
    @Query("SELECT * FROM todos WHERE category = :category ORDER BY dueDate ASC")
    fun getTodosByCategory(category: TodoCategory): Flow<List<TodoEntity>>

    // 根据优先级查询
    @Query("SELECT * FROM todos WHERE priority = :priority ORDER BY dueDate ASC")
    fun getTodosByPriority(priority: PriorityLevel): Flow<List<TodoEntity>>

    // 根据标签查询
    @Query("SELECT * FROM todos WHERE tags LIKE '%' || :tag || '%' ORDER BY dueDate ASC")
    fun getTodosByTag(tag: String): Flow<List<TodoEntity>>

    // 查询未完成的待办
    @Query("SELECT * FROM todos WHERE isCompleted = 0 ORDER BY dueDate ASC")
    fun getActiveTodos(): Flow<List<TodoEntity>>

    // 查询已完成的待办
    @Query("SELECT * FROM todos WHERE isCompleted = 1 ORDER BY dueDate DESC")
    fun getCompletedTodos(): Flow<List<TodoEntity>>

    // 查询即将到期的待办（未来3天内）
    @Query("SELECT * FROM todos WHERE dueDate BETWEEN :start AND :end AND isCompleted = 0 ORDER BY dueDate ASC")
    fun getUpcomingTodos(start: Date, end: Date): Flow<List<TodoEntity>>

    // 标记为完成
    @Query("UPDATE todos SET isCompleted = 1 WHERE id = :id")
    suspend fun markAsCompleted(id: Long)

    // 标记为未完成
    @Query("UPDATE todos SET isCompleted = 0 WHERE id = :id")
    suspend fun markAsIncomplete(id: Long)

    // 搜索功能
    @Query("SELECT * FROM todos WHERE title LIKE '%' || :query || '%' OR description LIKE '%' || :query || '%' ORDER BY dueDate ASC")
    fun searchTodos(query: String): Flow<List<TodoEntity>>
}